package com.yict.csms.resourceplan.service.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.yict.common.entity.PageEntity;
import com.yict.common.service.impl.CommonService;
import com.yict.csms.company.entity.Company;
import com.yict.csms.company.service.impl.CompanyServiceImpl;
import com.yict.csms.resourceplan.dao.impl.ShipManagerDaoImpl;
import com.yict.csms.resourceplan.dao.impl.ShipPlanDaoImpl;
import com.yict.csms.resourceplan.entity.ShipCsms;
import com.yict.csms.resourceplan.entity.ShipNGen;
import com.yict.csms.resourceplan.entity.ShipPlan;
import com.yict.csms.resourceplan.service.IShipManagerService;
import com.yict.csms.system.entity.DataDict;
import com.yict.csms.system.entity.Profession;
import com.yict.csms.system.service.impl.DataDictServiceImpl;
import com.yict.csms.system.service.impl.ProfessionServiceImpl;
import com.yict.csms.system.util.DictionaryUtils;

@Service
public class ShipManagerServiceImpl extends CommonService<ShipCsms, Long>
		implements IShipManagerService {

	@Autowired
	private ShipManagerDaoImpl shipDaoImpl;
	@Autowired
	private ShipPlanDaoImpl shipPlanDao;
	@Autowired
	private DataDictServiceImpl dataDictService;
	@Autowired
	private ProfessionServiceImpl professionService;
	@Autowired
	private CompanyServiceImpl companyServiceImpl;
	private PageEntity pageNGen;

	public List<ShipCsms> search(Map<String, Object> queryMap, PageEntity page) {
		pageEntity = new PageEntity();
		List<ShipCsms> list = new ArrayList<ShipCsms>();
		StringBuilder queryString = new StringBuilder();
		queryString.append("from ShipCsms as c where 1=1");
		if (queryMap != null) {
			if (queryMap.get("etbDate1") != null
					&& !"".equals(queryMap.get("etbDate1"))) {
				queryString
						.append(" and c.etb >= to_date(:etbDate1,'yyyy-MM-dd HH24:mi')");
			}
			if (queryMap.get("etbDate2") != null
					&& !"".equals(queryMap.get("etbDate2"))) {
				queryString
						.append(" and c.etb <= to_date(:etbDate1,'yyyy-MM-dd HH24:mi')");
			}
			if (queryMap.get("shipName") != null
					&& !"".equals(queryMap.get("shipName"))) {
				queryString.append(" and c.shipName like :shipName");
			}
			if (queryMap.get("voyage") != null
					&& !"".equals(queryMap.get("voyage"))) {
				queryString.append(" and c.voyage like :voyage");
			}
			if (queryMap.get("etb") != null && queryMap.get("tcc") != null) {
				queryString
						.append(" and ((c.etb <= :etb and c.tcc >= :etb) or (c.etb <= :tcc and c.tcc >= :tcc)) ");
			}
			if (queryMap.get("isAttendCar") != null
					&& !"".equals(queryMap.get("isAttendCar"))) {
				queryString.append(" and c.isAttendCar = :isAttendCar ");
			}
			
			if (queryMap.get("companyId") != null && !"".equals(queryMap.get("companyId"))) {
				queryString.append(" and s.shipid in (select sp.ship.shipId from ShipPlan sp where sp.company.companyid := companyId)");
			}

			if (queryMap.get("etdFromDate") != null && !"".equals(queryMap.get("etdFromDate"))) {
				queryString.append(" and s.etd >= to_date(:etdFromDate,'yyyy-MM-dd HH24:mi:ss')");
			}
			if (queryMap.get("etdToDate") != null && !"".equals(queryMap.get("etdToDate"))) {
				queryString.append(" and s.etd <= to_date(:etdToDate,'yyyy-MM-dd HH24:mi:ss')");
			}
			
		}
		list = shipDaoImpl.search(queryString.toString(), queryMap, page);
		pageEntity.setTotalRecord(shipDaoImpl.getPageEntity().getTotalRecord());
		return list;
	}

	public List<ShipNGen> searchNGen(Map<String, Object> queryMap,
			PageEntity page) {
		pageNGen = new PageEntity();
		List<ShipNGen> list = new ArrayList<ShipNGen>();
		StringBuilder queryString = new StringBuilder();
		queryString
				.append("select t1.VOYID_VESSEL_CODE SHIPNAME,t1.Voyid_Voyage_Code VOYAGE,"
						+ "(case when t2.Vessel_Type = 'NSSC' or t2.Vessel_Type = 'SSC' then 'Vessle' when t2.Vessel_Type = 'SSNC' or t2.Vessel_Type = 'LTR' or t2.Vessel_Type = 'YICT' then 'Barge' end) SHIPTYPE, "
						+ "t1.Quay_Crane_List QCLIST, t1.Berth_Id BERTHID, t1.Berthing_Datetime ETB,t3.Ld_Ds_Operation_Complete_Dt TCC,t1.Departure_Datetime ETD,"
						+ "(case when t1.Departure_Datetime_Status != null then t1.Departure_Datetime_Status else 'T' end) ETDSTUTAS,"
						+ "sum(case when t4.Shipment_Instruction_Type = 'PL' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' then 1 else 0 end) DS, "
						+ "sum(case when t4.Shipment_Instruction_Type = 'PS' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' then 1 else 0 end) LD, "
						+ "sum(case when t4.Shipment_Instruction_Type = 'PL' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' and t6.cntr_trade_mode = 'D' then 1 else 0 end) DSN, "
						+ "sum(case when t4.Shipment_Instruction_Type = 'PL' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' and t6.cntr_trade_mode = 'D' then 0 else 1 end) DSW,"
						+ "sum(case when t4.Shipment_Instruction_Type = 'PS' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' and t6.cntr_trade_mode = 'D' then 1 else 0 end) LDN, "
						+ "sum(case when t4.Shipment_Instruction_Type = 'PS' and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' and t6.cntr_trade_mode = 'D' then 0 else 1 end) LDW, "
						+ "sum(case when t4.Shipment_Instruction_Type in ('HL', 'HS') and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' then 1 else 0 end) RS,"
						+ "sum(case when t4.Shipment_Instruction_Type in ('RL', 'RS') and t4.SHIPMENT_INSTRUCTION_STATUS <> 'V' then 0 else 1 end) SH, t5.hatch_cover_move_count HC "
						+ "from VOYAGE t1 left join VOY_VESSEL_MODEL t2 on t1.VOYID_COMPANY_CODE = t2.Voyid_Company_Code and t1.VOYID_VESSEL_CODE = t2.Voyid_Vessel_Code "
						+ "and t1.VOYID_VOYAGE_CODE = t2.Voyid_Voyage_Code left join VOYAGE_OPERATIONAL_CONTROL t3 on t1.VOYID_COMPANY_CODE = t3.Voyid_Company_Code "
						+ "and t1.VOYID_VESSEL_CODE = t3.Voyid_Vessel_Code and t1.VOYID_VOYAGE_CODE = t3.Voyid_Voyage_Code "
						+ "left join SHIPMENT_INSTRUCTION t4 on t1.VOYID_COMPANY_CODE = t4.Voyid_Company_Code and t1.VOYID_VESSEL_CODE = t4.Voyid_Vessel_Code "
						+ "and t1.VOYID_VOYAGE_CODE = t4.Voyid_Voyage_Code left join cwp_job_bay_coverage t5 on t1.VOYID_COMPANY_CODE = t5.Voyid_Company_Code "
						+ "and t1.VOYID_VESSEL_CODE = t5.Voyid_Vessel_Code and t1.VOYID_VOYAGE_CODE = t5.Voyid_Voyage_Code "
						+ "left join SHIPMENT t6 on t4.cntr_id = t6.cntr_id  and t4.shipment_id = t6.shipment_id where 1=1");
		if (queryMap != null) {
			if (queryMap.get("etbDate1") != null
					&& !"".equals(queryMap.get("etbDate1"))) {
				queryString
						.append(" and t1.Berthing_Datetime >= to_date(?,'yyyy-MM-dd HH24:mi')");
			}
			if (queryMap.get("etbDate2") != null
					&& !"".equals(queryMap.get("etbDate2"))) {
				queryString
						.append(" and t1.Berthing_Datetime <= to_date(?,'yyyy-MM-dd HH24:mi')");
			}
			if (queryMap.get("shipName") != null
					&& !"".equals(queryMap.get("shipName"))) {
				queryString.append(" and t1.VOYID_VESSEL_CODE like ?");
			}
			if (queryMap.get("voyage") != null
					&& !"".equals(queryMap.get("voyage"))) {
				queryString.append(" and t1.Voyid_Voyage_Code like ?");
			}
		}
		queryString
				.append(" group by t1.VOYID_VESSEL_CODE, t1.Voyid_Voyage_Code, t2.Vessel_Type, t1.Quay_Crane_List, t1.Berth_Id, t1.Berthing_Datetime, t1.Departure_Datetime, "
						+ "t3.Ld_Ds_Operation_Complete_Dt, t1.Departure_Datetime, t1.Departure_Datetime_Status, t5.hatch_cover_move_count");
		try {
			list = shipDaoImpl.searchNGen(queryString.toString(), queryMap,
					page);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		pageNGen.setTotalRecord(shipDaoImpl.getPageNGen().getTotalRecord());
		return list;
	}

	public List<ShipCsms> searchShipData(HashMap<String, Object> map){
		String sql = "select c from ShipCsms as c left join c.shipPlanSet as s where c.shipId in (:ids) ";
		return shipDaoImpl.list(sql, map, 0, 0);
	}
	
	@Transactional(readOnly = false)
	public boolean update(List<ShipCsms> list) throws Exception {
		boolean bool = true;
		try {
			for (ShipCsms ship : list) {
				shipDaoImpl.update(ship);
			}
		} catch (Exception e) {
			bool = false;
			e.printStackTrace();
		}
		return bool;
	}

	public boolean verifyShipName(String shipId, String shipName, String voyage) {
		return shipDaoImpl.verifyShipName(shipId, shipName, voyage);
	}

	public boolean verifyQcList(String qc) {
		boolean bool = false;
		DataDict dict = dataDictService.findById(qc);
		if (dict != null && dict.getDatatype().equals("qc"))
			bool = true;
		return bool;
	}

	public boolean verifyBerthId(String berthId) {
		boolean bool = false;
		DataDict dict = dataDictService.findById(berthId);
		if (dict != null && dict.getDatatype().equals("berth"))
			bool = true;
		return bool;
	}

	public List<ShipCsms> comparisonData(HashMap<String, Object> map)
			throws Exception {
		List<ShipCsms> list = new ArrayList<ShipCsms>();
		StringBuilder queryString = new StringBuilder();
		queryString.append("from ShipCsms as c where 1=1");
		if (map != null) {
			if (map.get("shipName") != null && !"".equals(map.get("shipName"))) {
				queryString.append(" and shipName = :shipName");
			} else {
				throw new Exception("shipName is null");
			}
			if (map.get("voyage") != null && !"".equals(map.get("voyage"))) {
				queryString.append(" and voyage = :voyage and (");
			} else {
				throw new Exception("voyage is null and (");
			}
			if (map.get("qcList") != null && !"".equals(map.get("qcList"))) {
				queryString.append(" qcList != :qcList");
			} else {
				queryString.append(" qcList is not null");
			}
			if (map.get("berthId") != null && !"".equals(map.get("berthId"))) {
				queryString.append(" or berthId != :berthId");
			} else {
				queryString.append(" or berthId is not null");
			}
			if (map.get("ETB") != null && !"".equals(map.get("ETB"))) {
				queryString
						.append(" or ETB != to_date(:ETB,'yyyy-MM-dd HH24:mi')");
			} else {
				queryString.append(" or ETB is not null");
			}
			if (map.get("ETD") != null && !"".equals(map.get("ETD"))) {
				queryString
						.append(" or ETD != to_date(:ETD,'yyyy-MM-dd HH24:mi')");
			} else {
				queryString.append(" or ETD is not null");
			}
			if (map.get("TCC") != null && !"".equals(map.get("TCC"))) {
				queryString
						.append(" and TCC != to_date(:TCC,'yyyy-MM-dd HH24:mi')");
			} else {
				queryString.append(" and TCC is not null");
			}
			if (map.get("DS") != null && !"".equals(map.get("DS"))) {
				queryString.append(" or DS != :DS");
			} else {
				queryString.append(" or DS is not null");
			}
			if (map.get("LD") != null && !"".equals(map.get("LD"))) {
				queryString.append(" or LD != :LD");
			} else {
				queryString.append(" or LD is not null");
			}
			if (map.get("RS") != null && !"".equals(map.get("RS"))) {
				queryString.append(" or RS != :RS");
			} else {
				queryString.append(" or RS is not null");
			}
			if (map.get("SH") != null && !"".equals(map.get("SH"))) {
				queryString.append(" or SH != :SH");
			} else {
				queryString.append(" or SH is not null");
			}
			if (map.get("HC") != null && !"".equals(map.get("HC"))) {
				queryString.append(" or HC != :HC)");
			} else {
				queryString.append(" or HC is not null)");
			}
		}
		list = shipDaoImpl.search(queryString.toString(), map, null);
		return list;
	}

	@Transactional(readOnly = false, rollbackFor = { Exception.class })
	public boolean copyShip(List<ShipCsms> delList, List<ShipCsms> saveList) {
		boolean bool = false;
		try {
			if (delList != null) {
				bool = shipDaoImpl.remove(delList);
			}
			bool = shipDaoImpl.saveOrUpdate(saveList);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		}
		return bool;
	}

	public List<Company> findCompany() throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		DataDict dict0 = dataDictService.findByCode(DictionaryUtils.SHIPPROTYPE01);
		Profession prof0 = professionService.findByCode(dict0.getDatavalue());
//		DataDict dict1 = dataDictService.findByCode(DictionaryUtils.SHIPPROTYPE02);
//		Profession prof1 = professionService.findByCode(dict1.getDatavalue());
		if(prof0!=null){
		map.put("type01", prof0.getProfecode());
		}else{
			throw new Exception("未找到工种箱船操作");
		}
//		if(prof1!=null){
//		map.put("type02", prof1.getProfecode());
//		}else{
//			throw new Exception("未找到工种外船理货");
//		}
		return companyServiceImpl.getCompanyList(map);
	}

	public List<Object[]> searchCompany() {
		LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
		String sql = "select t2.companyid, t2.companyname,(CASE WHEN t1.etdstutas ='F' THEN sum(1) else 0 END) as shipNumf, (CASE WHEN t1.etdstutas ='F' THEN "
				+ "t1.ld+t1.ds else 0 END) as dslsf,(CASE WHEN t1.etdstutas !='F' THEN sum(1) else 0 END) as shipNum, (CASE WHEN t1.etdstutas !='F' THEN t1.ld+t1.ds else 0"
				+ "END) as dsls from csms_company t2 left join csms_ship_plan t on t.companyid = t2.companyid left join csms_ship t1 on t.shipid = t1.shipid "
				+ "where t2.companyid in (:companyIds) group by t2.companyid,t2.companyname,t1.ld,t1.ds,t1.etdstutas";
		List<Object[]> list0 = new ArrayList<Object[]>();
		try {
			List<Company> companyList = this.findCompany();
			if(companyList!=null&&companyList.size()>0){
			Long[] companyIds = new Long[companyList.size()];
			int i = 0;
			for (Company company : companyList) {
				companyIds[i] = company.getCompanyId();
				i++;
			}
			map.put("companyIds", companyIds);
			list0 = shipDaoImpl.listSql(sql, map, 0, 0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list0;
	}

	@Transactional(readOnly = false, rollbackFor = { Exception.class })
	public boolean delete(List<ShipCsms> t) throws Exception {
		boolean bool = false;
		try {
			for (ShipCsms t2 : t) {
				List<ShipPlan> planList = shipPlanDao.findByShipId(t2
						.getShipId());
				bool = shipPlanDao.remove(planList);
				bool = shipDaoImpl.remove(t2);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		}
		return bool;
	}

	public ShipCsms findById(Long id) {
		return shipDaoImpl.findbyId(id);
	}

	public PageEntity getPageNGen() {
		return pageNGen;
	}

	public void setPageNGen(PageEntity pageNGen) {
		this.pageNGen = pageNGen;
	}
}
